<?php
require_once(ROOT . LIBRARY .'/excel/PHPExcel.php');

$FIELDS = fields::getInstance();
$fieldsList = $FIELDS->splitFields(
   array(
      'belong' => 'a', 
      'active' => 1,
      'type' => array('logic', 'string', 'types', 'select', 'multiselect', 'email', 'integer')
   )
);

# EXPORT
if($action == 1)
{
   $sql = 'SELECT * FROM `meccano_articles` AS `a` LEFT JOIN `meccano_articles_translate` AS `at` ON a.id = at.id AND at.lang = '. request::getLang('defaultId');
   
   $where = array('`deleted` IS NULL');
   if(!empty($_POST['f']) && !empty($_POST['f']['category']))
   {
      $cats = $FILTER->filterForInteger($_POST['f']['category']);
      if(!empty($cats))
      {
         $where[] = ' `a`.`cat` IN('. join(', ', $cats) .')';
      }
   }
   
   $sql .= ' WHERE '. join(' AND ', $where) .' ORDER BY `cat`';
   $DB->sql = $sql;
   $DB->DB_Fetch();
   
   if(!empty($DB->fetched))
   {
      $Excel = new PHPExcel();
      $Excel->getProperties()
              ->setCreator("Meccano ArtiNet")
              ->setTitle("Export ". config::read('domain', 'file'))
              ->setSubject("Export ". config::read('domain', 'file'));
      
      $cells = array();
      $c     = 4;
      $postFields = !empty($_POST['f']['field']) ? $_POST['f']['field'] : NULL;
      
      $Excel->setActiveSheetIndex(0)
              ->setCellValue(getExcelLeter(1) .'1', 'id')
              ->setCellValue(getExcelLeter(2) .'1', 'cat')
              ->setCellValue(getExcelLeter(3) .'1', 'category');
      
      foreach($fieldsList AS $f)
      {
         if(empty($postFields) || in_array($f->alias, $postFields))
         {
            $cells[$f->alias]['l'] = getExcelLeter($c);
            $cells[$f->alias]['f'] = $f;

            $c++;
            $Excel->setActiveSheetIndex(0)->setCellValue($cells[$f->alias]['l'] .'1', $f->alias);
         }
      }
      
      $r = 2;
      foreach($DB->fetched AS $row)
      {
         $Excel->setActiveSheetIndex(0)
                 ->setCellValue(getExcelLeter(1) . $r, $row['id'])
                 ->setCellValue(getExcelLeter(2) . $r, $row['cat'])
                 ->setCellValue(getExcelLeter(3) . $r, $categories[$row['cat']]->title);
         foreach($cells AS $f_alias => $c)
         {
            $value = $c['f']->normalizeValue($row[$f_alias]);
            $value = is_array($value) ? $value = join(',', $value) : (string)$value;
            $Excel->setActiveSheetIndex(0)->setCellValue($c['l'] . $r, $value);
         }
         $r++;
      }
      
      header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      header('Content-Disposition: attachment;filename="export-data.xlsx"');
      header('Cache-Control: max-age=0');

      $objWriter = PHPExcel_IOFactory::createWriter($Excel, 'Excel2007');
      $objWriter->save('php://output');
      exit();
   }
   
}


# IMPORT
if($action == 2)
{
   $f = $REQUEST->dataFiles('f');
   
   if(!empty($f['import']))
   {
      include ROOT . LIBRARY .'/excel/PHPExcel/IOFactory.php';
      $Excel = PHPExcel_IOFactory::load($f['import']['tmp_name']);
      $sheetData = $Excel->getActiveSheet()->toArray(null,false,true,true);
      if(is_array($sheetData) && sizeof($sheetData) > 1)
      {
         # BUILD HEADER
         $head = array();
         $tmp  = array_shift($sheetData);
         foreach($tmp AS $c => $f_alias)
         {
            if(!empty($f_alias) && ($f = $FIELDS->isFieldExist($f_alias, 'a')) !== false)
            {
               $head[$c] = $f;
            } elseif($f_alias == 'id' || $f_alias == 'cat')
            {
               $head[$c] = $f_alias;
            }
         }
         
         while($row = array_shift($sheetData))
         {
            $update = false;
            $insert = true;
            $sql    = array();
            $sql_t  = array();
            
            if(!empty($row))
            {
               foreach($head AS $c => $f)
               {
                  if($f == 'id' && is_numeric($row[$c]))
                  {
                     $update = $row[$c];
                  } elseif($f == 'cat')
                  {
                     if(is_numeric($row[$c]) && $TREE->getNode($row[$c])) $insert = $row[$c];
                  } elseif(is_object($f))
                  {
                     $value = $f->validateInput($row[$c]);
                     if(is_array($value)) $value = join(',', $value);

                     if($f->translate)
                     {
                        $sql_t[] = 'at.`'. $f->alias .'` = "'. $value .'"';
                     } else
                     {
                        $sql[] = 'a.`'. $f->alias .'` = "'. $value .'"';
                     }
                  }
               }
               
               if($update)
               {
                  $sql[] = 'a.`mdate` = "'. date('Y.m.d H:i:s') .'"';
                  $sql[] = 'a.`user` = '. session::user()->id;
                  if(!empty($sql_t)) $sql = array_merge($sql, $sql_t);
                  $sql = 'UPDATE `meccano_articles` AS `a`, `meccano_articles_translate` AS `at` SET '. join(', ', $sql) .' 
                     WHERE `a`.`id` = '. $update .' AND `a`.`id` = `at`.`id` AND `at`.`lang` = '. request::getLang('defaultId');
                  $DB->sql = $sql;
                  $DB->DB_Query();
                  
               } elseif($insert)
               {
                  $sql[] = 'a.`cat` = '. $insert;
                  $sql[] = 'a.`user` = '. session::user()->id;
                  $sql[] = 'a.`mdate` = "'. date('Y.m.d H:i:s') .'"';
                  $sql[] = 'a.`cdate` = "'. date('Y.m.d H:i:s') .'"';
                  $sql = 'INSERT INTO `meccano_articles` AS `a` SET '. join(', ', $sql);
                  $DB->sql = $sql;
                  if(!empty($sql_t) && $DB->DB_Query())
                  {
                     $sql_t[] = 'at.id = '. $DB->DB_LastID();
                     $sql_t[] = 'at.lang = '. request::getLang('defaultId');

                     $sql = 'INSERT INTO `meccano_articles_translate` AS `at` SET '. join(', ', $sql_t);
                     $DB->sql = $sql;
                     $DB->DB_Query($sql);
                  }
               }
            }
         }
      }
      
      $ErrorHandler->setSuccess(array(
         'varkey' => 'import'
      ));
   }
}


# GET TREE NODES TO EXPORT
$jsonString = array();
foreach($TREE->getTree() AS $n)
{
   if(in_array('topmenu', $n->types)) $jsonString[] = makeJsonEntry($n);
}


$SMARTY->assign('jsonString', json_encode($jsonString));
$SMARTY->assign('fields', $fieldsList);
$layout = 'export.html';


function makeJsonEntry($n)
{
   $entry = new stdClass();
   $entry->data = $n->title;
   $entry->attr['id'] = $n->id;
   //$entry->state = 'open';
   
   if(!empty($n->childs))
   {
      $entry->children = array();
      foreach($n->childs AS $n)
      {
         $entry->children[] = makeJsonEntry($n);
      }
   }
   
   return $entry;
}


function getExcelLeter($n)
{
   $columns = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');
   $up = floor($n / sizeof($columns));
   $n  = $n % sizeof($columns);
   $str = '';
   if($up) $str .= getExcelLeter($up);
   $str .= $columns[$n-1];
   
   return $str;
}